package com.br.objetos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.br.outros.Conexao;
import com.br.outros.MailSenderMobile;
import com.thoughtworks.xstream.XStream;
import com.thoughtworks.xstream.io.xml.DomDriver;

public class BarMobile {

	private String sql = "";

	public boolean gravar(BarBeanMobile bean) {
		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";

		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			Statement stmt = con.createStatement();

			sql = "INSERT INTO BAR (NOMEFANTASIA, RAZAOSOCIAL, CNPJ, LOGIN, SENHA, CEP, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO, CIDADE, ESTADO, PAIS, LATITUDE, LONGITUDE) VALUES"
					+ "(";
			sql += "'" + bean.getNomeFantasia() + "',";
			sql += "'" + bean.getRazaoSocial() + "',";
			sql += "" + bean.getCnpj() + ",";
			sql += "'" + bean.getLogin() + "',";
			sql += "'" + bean.getSenha() + "',";
			sql += "" + bean.getCep() + ",";
			sql += "'" + bean.getEndereco() + "',";
			sql += "" + bean.getNumero() + ",";
			sql += "'" + bean.getComplemento() + "',";
			sql += "'" + bean.getBairro() + "',";
			sql += "'" + bean.getCidade() + "',";
			sql += "'" + bean.getEstado() + "',";
			sql += "'" + bean.getPais() + "',";
			sql += "" + bean.getLatitude() + ",";
			sql += "" + bean.getLongitude() + ")";
			// con.commit();
			stmt.executeUpdate(sql);
			// String pagina = "";
			// pagina = "AreaAdmin.jsp";

			// System.out.println("Bar cadastrado com sucesso!");

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - GRAVAR<BR />\n"
					+ e.toString());
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - GRAVAR<BR />\n"
					+ e.toString());
			return false;
		}
		return true;

	}

	public boolean AutenticarBar(String p_login, String p_senha) {
		Statement st = null;
		ResultSet rs = null;
		boolean resultado = false;

		Connection con;
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT * FROM BAR WHERE SENHA = '" + p_senha
					+ "' AND LOGIN = '" + p_login + "'";

			// Autenticar
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				BarBeanMobile bean = new BarBeanMobile();
				bean.setIdBar(Integer.valueOf(rs.getString("IDBAR")));
				bean.setNomeFantasia(rs.getString("NOMEFANTASIA"));
				bean.setRazaoSocial(rs.getString("RAZAOSOCIAL"));
				bean.setCnpj(rs.getString("CNPJ"));
				bean.setLogin(rs.getString("LOGIN"));
				bean.setSenha(rs.getString("SENHA"));
				bean.setCep(rs.getString("CEP"));
				bean.setEndereco(rs.getString("ENDERECO"));
				bean.setNumero(Integer.valueOf(rs.getString("NUMERO")));
				bean.setComplemento(rs.getString("COMPLEMENTO"));
				bean.setBairro(rs.getString("BAIRRO"));
				bean.setCidade(rs.getString("CIDADE"));
				bean.setEstado(rs.getString("ESTADO"));
				bean.setPais(rs.getString("PAIS"));
				bean.setLatitude(rs.getString("LATITUDE"));
				bean.setLongitude(rs.getString("LONGITUDE"));

				resultado = true;
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - AutenticarBar<BR />\n"
					+ e.toString());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - AutenticarBar<BR />\n"
					+ e.toString());
		}

		return resultado;
	}

	public static String serialize(BarBeanMobile obj) {
		XStream xstream = new XStream(new DomDriver());
		xstream.setMode(XStream.NO_REFERENCES);
		xstream.alias("bar", BarBeanMobile.class);
		String serialize = xstream.toXML(obj);
		return serialize;
	}

	public String getPromocaoByBar(int idBar) {
		// pegar promocoes por bar
		// descricao - R$ valor
		Connection con;
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		String res = "";
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			sql = "SELECT DESCRICAO AS DESCRICAO, VALOR AS VALOR FROM PROMOCAO WHERE CURDATE() <=  DATAVALIDADE AND IDBAR = "
					+ idBar + "; ";
			Statement st = null;
			ResultSet rs = null;
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				res += rs.getString("DESCRICAO") + " - R$ "
						+ rs.getDouble("VALOR") + ";";

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getPromocaoByBar<BR />\n"
					+ e.toString());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}

	public ArrayList<BarBeanMobile> getCoordenadasBares() {
		Statement st = null;
		ResultSet rs = null;
		ArrayList<BarBeanMobile> lista = new ArrayList<BarBeanMobile>();

		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT IDBAR AS IDBAR, NOMEFANTASIA AS NOMEFANTASIA, RAZAOSOCIAL AS RAZAOSOCIAL, CNPJ AS CNPJ, LOGIN AS LOGIN, SENHA AS SENHA,"
					+ "CEP AS CEP, ENDERECO AS ENDERECO, NUMERO AS NUMERO, COMPLEMENTO AS COMPLEMENTO, BAIRRO AS BAIRRO, CIDADE AS CIDADE, ESTADO AS ESTADO, "
					+ "PAIS AS PAIS, LATITUDE AS LATITUDE, LONGITUDE AS LONGITUDE FROM BAR";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				BarBeanMobile bean = new BarBeanMobile();
				bean.setBairro(rs.getString("BAIRRO"));
				bean.setCep(rs.getString("CEP"));
				bean.setCidade(rs.getString("CIDADE"));
				bean.setCnpj(rs.getString("CNPJ"));
				bean.setComplemento(rs.getString("COMPLEMENTO"));
				bean.setEndereco(rs.getString("ENDERECO"));
				bean.setEstado(rs.getString("ESTADO"));
				bean.setIdBar(Integer.valueOf(rs.getString("IDBAR")));
				bean.setLatitude(rs.getString("LATITUDE"));
				bean.setLogin(rs.getString("LOGIN"));
				bean.setLongitude(rs.getString("LONGITUDE"));
				bean.setNomeFantasia(rs.getString("NOMEFANTASIA"));
				bean.setNumero(Integer.valueOf(rs.getString("NUMERO")));
				bean.setPais(rs.getString("PAIS"));
				bean.setRazaoSocial(rs.getString("RAZAOSOCIAL"));
				bean.setSenha(rs.getString("SENHA"));

				lista.add(bean);
				// bean.setLista(lista);
			}

			// return id;
			// System.out.println(sql);
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getCoordenadasBares<BR />\n"
					+ e.toString());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getCoordenadasBares<BR />\n"
					+ e.toString());
		}

		return lista;
	}

	public BarBeanMobile getBar(int idBar) {
		Statement st = null;
		ResultSet rs = null;
		BarBeanMobile bean = new BarBeanMobile();

		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT IDBAR AS IDBAR, NOMEFANTASIA AS NOMEFANTASIA, RAZAOSOCIAL AS RAZAOSOCIAL, CNPJ AS CNPJ, LOGIN AS LOGIN, SENHA AS SENHA,"
					+ "CEP AS CEP, ENDERECO AS ENDERECO, NUMERO AS NUMERO, COMPLEMENTO AS COMPLEMENTO, BAIRRO AS BAIRRO, CIDADE AS CIDADE, ESTADO AS ESTADO, "
					+ "PAIS AS PAIS, LATITUDE AS LATITUDE, LONGITUDE AS LONGITUDE FROM BAR WHERE IDBAR = "
					+ idBar;

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				bean = new BarBeanMobile();
				bean.setBairro(rs.getString("BAIRRO"));
				bean.setCep(rs.getString("CEP"));
				bean.setCidade(rs.getString("CIDADE"));
				bean.setCnpj(rs.getString("CNPJ"));
				bean.setComplemento(rs.getString("COMPLEMENTO"));
				bean.setEndereco(rs.getString("ENDERECO"));
				bean.setEstado(rs.getString("ESTADO"));
				bean.setIdBar(Integer.valueOf(rs.getString("IDBAR")));
				bean.setLatitude(rs.getString("LATITUDE"));
				bean.setLogin(rs.getString("LOGIN"));
				bean.setLongitude(rs.getString("LONGITUDE"));
				bean.setNomeFantasia(rs.getString("NOMEFANTASIA"));
				bean.setNumero(Integer.valueOf(rs.getString("NUMERO")));
				bean.setPais(rs.getString("PAIS"));
				bean.setRazaoSocial(rs.getString("RAZAOSOCIAL"));
				bean.setSenha(rs.getString("SENHA"));

				// bean.setLista(lista);
			}

			// return id;
			// System.out.println(sql);
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getBar<BR />\n"
					+ e.toString());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getBar<BR />\n"
					+ e.toString());
		}

		return bean;
	}

	public static ArrayList<Integer> getAllIds() {
		Statement st = null;
		ResultSet rs = null;
		BarBeanMobile bean = new BarBeanMobile();
		ArrayList<Integer> idsBares = new ArrayList<Integer>();
		Connection con;
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT IDBAR FROM BAR";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				idsBares.add(Integer.parseInt(rs.getString("IDBAR")));
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getBar<BR />\n"
					+ e.toString());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO BAR MOBILE - getBar<BR />\n"
					+ e.toString());
		}

		return idsBares;
	}

	public void setAutenticado(boolean b) {
		// TODO Auto-generated method stub

	}
}
